'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：deal_excel.py
#日期：2018-10-21
#备注：pip install xlwt, pip install xlrd, pip install xlutils，pip install openpyxl
#模块分类：2003以前的表格以.xls后缀，用 xlwt 来写表格，用xlrd来读取表格；搭配xlutils修改表格
          2007的表格以.xlsx后缀，用openpyxl来读写表格。  
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import xlwt
import xlrd
from xlutils.copy import copy
import os
from openpyxl import load_workbook
import pandas as pd

tall_style = xlwt.easyxf('font:height 250;')


# 获取excel数据，参数：file-文件;st_row-开始行数索引，st_col-开始列数索引，end_col-结束列数索引+1
def get_info(file,st_row,st_col,end_col):
    excel = xlrd.open_workbook(file)             # 打开excle文档读取数据
    table = excel.sheets()[0]
    nrows = table.nrows                          # 获取该sheet中的有效行数
    ncols = table.ncols                          # 获取列表的有效列数
    data = []
    print('xls格式sheet页总行数为：',nrows,'列数为：',ncols)
    for i in range(st_row,nrows):
        row = []
        for j in range(st_col,end_col):
            row.append(table.cell_value(i, j))
        data.append(row)
    #print(data)
    return data


# 参数：file-文件;st_row-开始行数索引，col-固定列数索引  data-一维列表
def get_one_line(file,st_row,col):
    excel = xlrd.open_workbook(file)             # 打开excle文档读取数据
    table = excel.sheets()[0]
    nrows = table.nrows                          # 获取该sheet中的有效行数
    data = []
    print('xls格式sheet页需要统计的总行数为：',nrows-st_row)
    for i in range(st_row,nrows):
        data.append(table.cell_value(i,col).strip().lower())  # 忽略大小写
    data.sort()  # 排序
    return data


# 新建excel写入
def common_write(twoList,file_path,file,sheet_name,st_row,st_col):
    if not os.path.exists(file_path):
        os.makedirs(file_path)
    wb = xlwt.Workbook()                                # 打开一个空白excel
    sheet = wb.add_sheet(sheet_name)                    # 添加表格名称
    for i in range(0,len(twoList)):
        for j in range(0,len(twoList[i])):
            sheet.write(i+st_row,j+st_col,twoList[i][j])              # i,j 分别代表行和列
    wb.save(file_path + file)
    print("写入表格成功！！！")

# 旧excel直接更新数据 st_row st_col
def common_write_x(twoList,file_path,file,st_row,st_col):
    data = load_workbook(file_path + file)  # openpyxl模块只支持xlxs
    sheets = data.sheetnames
    table = data[sheets[0]]
    for i in range(0,len(twoList)):
        for j in range(0, len(twoList[i])):
            table.cell(row=i+st_row, column=j+st_col).value=twoList[i][j]
    data.save(file_path + file)
    print("写入表格成功！！！")

# 旧excel，新建sheet 删除行数据 st_row st_col
def common_delete(file_path,file,sheet_name,st_row,col):
    data = pd.read_excel(file_path+file, sheet_name='Sheet1')
    df = data.dropna(how='all')
    #print(data['价格'])
    if df['价格']=='超值费':
        df.drop('price', axis=1)
    df.to_excel('F:\PythonProject\homework\\bill\\excel_to_python.xls', sheet_name='sheet', index=False)

# 旧excel，旧sheet 后更新数据 st_row st_col
def common_update(twoList,file_path,file,st_row,st_col):
    data = xlrd.open_workbook(file_path + file,formatting_info=True)
    new_excel = copy(data)
    sheet = new_excel.get_sheet(0)
    for i in range(0,len(twoList)):
        for j in range(0, len(twoList[i])):
            sheet.write(i+st_row, j+st_col,twoList[i][j])
    new_excel.save(file_path + file)
    print("写入表格成功！！！")

# 旧excel，新建sheet 后更新数据 st_row st_col
def common_new_sheet(twoList,file_path,file,sheet_name,st_row,st_col):
    data = xlrd.open_workbook(file_path + file,formatting_info=True)
    new_excel = copy(data)
    sheet = new_excel.add_sheet(sheet_name)         # 添加表格名称
    for i in range(0,len(twoList)):
        for j in range(0, len(twoList[i])):
            sheet.write(i+st_row, j+st_col,twoList[i][j])
    new_excel.save(file_path + file)
    print("写入表格成功！！！")


# 复制excel,新建sheet后按行写入copy
def copy_new_sheet(twoList,file_path,old_file,file,sheet_name,st_row,st_col):
    if not os.path.exists(file_path):
        os.makedirs(file_path)
    data = xlrd.open_workbook(file_path +old_file, formatting_info=True)
    new_excel = copy(data)
    sheet = new_excel.add_sheet(sheet_name)         # 添加表格名称
    for i in range(0,len(twoList)):
        for j in range(0,len(twoList[i])):
            row_high = sheet.row(i)
            row_high.set_style(tall_style)
            sheet.write(i+st_row,j+st_col,twoList[i][j])           # i,j 分别代表行和列

    new_excel.save(file_path + file)
    print("写入表格成功！！！")


# 复制excel,原有sheet更新 st_row-开始行数索引，st_col-开始列数索引
def copy_update_sheet(twoList,file_path,old_file,file,sheet_name,st_row,st_col):
    if not os.path.exists(file_path):
        os.makedirs(file_path)
    data = xlrd.open_workbook(file_path + old_file, formatting_info=True)
    table = data.sheets()[0]
    nrows = table.nrows
    new_excel = copy(data)
    sheet = new_excel.get_sheet(0)         # 获取原有sheet
    for i in range(len(twoList)):
        for j in range(len(twoList[i])):
            row_high = sheet.row(i + nrows)
            row_high.set_style(tall_style)
            sheet.write(st_row + i,j+st_col,twoList[i][j])           # i,j 分别代表行和列

    new_excel.save(file_path + file)
    print("写入表格成功！！！")


# 创建新excel按列插入一列数据
def column_write(oneList,file_path,sheet_name):
    wb = xlwt.Workbook()                            # 打开一个空白excel
    sheet = wb.add_sheet(sheet_name)                # 添加表格名称
    for i in range(0,len(oneList)):
        sheet.write(i,0,oneList[i])                 # i,j 分别代表行和列
    wb.save(file_path)
    print("写入表格成功！！！")


# 创建新excel按行插入一行数据
def row_write(oneList,file_path,sheet_name):
    wb = xlwt.Workbook()                            # 打开一个空白excel
    sheet = wb.add_sheet(sheet_name)                # 添加表格名称
    for i in range(0,len(oneList)):
        sheet.write(0,i,oneList[i])                 # i,j 分别代表行和列
    wb.save(file_path)
    print("写入表格成功！！！")


if __name__ == '__main__':
    common_delete('F:\PythonProject\homework\\bill\\','202308.xls','test',10,10)
    #row_write(['1','2'], 'E:\zenglingwei\\test\example.xls','test')



